package bookmarks;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Optional;

import javax.naming.spi.DirStateFactory.Result;

import org.springframework.stereotype.Component;

@Component("BookMarkRepository")
public class BookMarkMysqlRepository implements BookMarkRepository {

    @Override
    public Collection<BookMark> findByAccountUsername(final String username) {
        Connection connection = null;
        Collection<BookMark> bookmarks = new ArrayList<BookMark>();
        try {
            connection = this._openConnection();
            bookmarks = this._findByAccountUsername(connection, username);
        } catch (Exception e) {
            //TODO: log
        } finally {
            this._closeCollection(connection);
            return bookmarks;
        }
    }

    @Override
    public Optional<BookMark> findById(final Long id) {
        Connection connection = null;
        Optional<BookMark> bookmark = Optional.empty();
        try {
            connection = this._openConnection();
            bookmark = this._findById(connection, id);
        } catch (Exception e) {
            //TODO: log
        } finally {
            this._closeCollection(connection);
            return bookmark;
        }
    }

    @Override
    public Optional<BookMark> save(final BookMark inputBookmark) {
        Connection connection = null;
        Optional<BookMark> outputBookmark = Optional.empty();
        try {
            connection = this._openConnection();
            outputBookmark = this._saveBookMark(connection, inputBookmark);
        } catch (Exception e) {
            //TODO: log
        } finally {
            this._closeCollection(connection);
            return outputBookmark;
        }
    }

    private Connection _openConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        return DriverManager.getConnection("jdbc:mysql://localhost/bookmarks", "user", "");
    }

    private void _closeCollection(Connection connection) {
        try {
            connection.close();
        } catch (Exception e) {
            // TODO: log
        }
    }

    private Collection<BookMark> _findByAccountUsername(final Connection connection, final String username) throws Exception {
        // TODO: rewrite in jOOQ
        final PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM BookMark, Account WHERE BookMark.accountId = Account.id AND Account.username = ?");
        preparedStatement.setString(1, username);
        final ResultSet resultSet = preparedStatement.executeQuery();
        final List<BookMark> bookmarks = new ArrayList<BookMark>();
        while (resultSet.next()) {
            final Long id = resultSet.getLong("id");
            final String uri = resultSet.getString("uri");
            final String description = resultSet.getString("description");
            bookmarks.add(new bookmarks.BookMark(id, uri, description));
        }
        resultSet.close();
        preparedStatement.close();
        return bookmarks;
    }

    private Optional<BookMark> _findById(final Connection connection, final Long inputId) throws Exception {
        // TODO: rewrite in jOOQ
        final PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM BookMark WHERE id = ?");
        preparedStatement.setLong(1, inputId);
        final ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return Optional.empty();
        }
        final Long id = resultSet.getLong("id");
        final String uri = resultSet.getString("uri");
        final String description = resultSet.getString("description");
        resultSet.close();
        preparedStatement.close();
        return Optional.of(new BookMark(id, uri, description));
    }

    private Optional<BookMark> _saveBookMark(final Connection connection, final BookMark inputBookmark) throws Exception {
        // TODO: rewrite in jOOQ
        final PreparedStatement insertStatement = connection.prepareStatement("INSERT INTO BookMark VALUES (default, ?, ?, ?)");
        insertStatement.setString(1, inputBookmark.getUri());
        insertStatement.setString(2, inputBookmark.getDescription());
        insertStatement.setLong(3, inputBookmark.getAccount().getId());
        insertStatement.executeUpdate();
        insertStatement.close();

        final PreparedStatement getIdStatement = connection.prepareStatement("SELECT LAST_INSERT_ID() FROM BookMark");
        final ResultSet resultSet = getIdStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            getIdStatement.close();
            return Optional.empty();
        }
        final Long id = resultSet.getLong(1);
        resultSet.close();
        getIdStatement.close();
        return Optional.of(new BookMark(id, inputBookmark.getUri(), inputBookmark.getDescription()));
    }


}